package com.ssy.dao;

import com.ssy.common.PageInfo;
import com.ssy.common.Result;
import com.ssy.entity.Class;
import com.ssy.entity.Major;
import com.ssy.entity.Student;
import com.ssy.vo.CurriculumVo;
import com.ssy.vo.ScoreVo;
import com.ssy.vo.StudentVo;

import java.util.List;
import java.util.Map;

/**
 * StudentDao
 *
 * @author ssy
 * @date 2020/11/16
 **/
public class StudentDao extends BaseDao {

    public PageInfo<StudentVo> selectPage(Map<String, Object> params, Integer pageNo, Integer limitNo) {
        String sql = "SELECT s_id AS sId,c.c_id AS cId,m.m_id AS mId,s.number AS number,NAME AS sname,sex AS sex,birthday AS birthday,\n" +
                "m.majorName AS major , c.cname AS className,address AS address,phone AS phone ,remarks AS remarks\n" +
                "FROM student s INNER JOIN class c  ON s.s_class=c.c_id INNER JOIN major m ON s.m_id=m.m_id where";
        if (params.containsKey("number")) {
            sql = sql + " s.number like '%" + params.get("number") + "%'  and";
        }
        if (params.containsKey("sname")) {
            sql = sql + " name like '%" + params.get("sname") + "%'  and";
        }
        if (params.containsKey("sex")) {
            sql = sql + " sex like '%" + params.get("sex") + "%'  and";
        }
        if (params.containsKey("majorId")) {
            sql = sql + " m.m_id = " + params.get("majorId") + "  and";
        }
        if (params.containsKey("className")) {
            sql = sql + " c.cname like '%" + params.get("className") + "%'  and";
        }
        if (params.containsKey("phone")) {
            sql = sql + " phone like '%" + params.get("phone") + "%'  and";
        }
        if (sql.endsWith("and")) {
            sql = sql.substring(0, sql.lastIndexOf("and"));
        }
        if (sql.endsWith("where")) {
            sql = sql.substring(0, sql.lastIndexOf("where"));
        }
        return this.selectPage(sql, StudentVo.class, pageNo, limitNo);
    }

    public Result selectAllMajor() {
        String sql = "SELECT m_id AS mId,majorName AS majorName FROM major";
        List<Major> majorList = this.selectList(sql, Major.class);
        return new Result(majorList);
    }

    public Result selectAllClassName() {
        String sql = "SELECT c_id AS cId,cname AS cname FROM class GROUP BY c_id";
        List<Class> classeList = this.selectList(sql, Class.class);
        return new Result(classeList);
    }

    public boolean insertStudent(String number, String sname, String sex, String birthday, int mId, int cId, String address, String phone, String remarks) {
        String sql = "INSERT INTO student(number,NAME,sex,birthday,m_id,s_class,address,phone,remarks) VALUE(?,?,?,?,?,?,?,?,?)";
        int studentId = this.insert(sql, number, sname, sex, birthday, mId, cId, address, phone, remarks);
        return studentId > 0 ? true : false;
    }

    public boolean updateStudent2(int sId, String number, String sname, String sex, String birthday, int mId, int cId, String address, String phone, String remarks) {
        String sql = "UPDATE student SET number=?,NAME=?,sex=?,birthday=?,m_id=?,s_class=?,address=?,phone=?,remarks=? WHERE s_id=?";
        return this.update(sql, number, sname, sex, birthday, mId, cId, address, phone, remarks, sId);
    }

    public boolean batchDeleteUser(String[] ids) {
        String sql = "delete from student where s_id=?";
        return this.batchDelete(sql, ids);

    }

    public boolean deleteStudentById(int id) {
        String sql = "delete from student where s_id=?";
        return this.update(sql, id);
    }

    public Student queryStudentByStudentNum(String studentNumberId) {
        String sql = "SELECT s_id AS sId,s_class AS Sclass FROM student WHERE number=?";
        return this.selectOne(sql, Student.class, studentNumberId);
    }

    public Student selectStudentByPhone(String phone) {
        String sql = "SELECT s_id AS sId,s_class AS Sclass FROM student WHERE phone=?";
        return this.selectOne(sql, Student.class, phone);
    }

    public Student selectStudentBySid(String sId) {
        String sql = "SELECT s_id AS sId,number AS number,phone AS phone FROM student WHERE s_id=?";
        return this.selectOne(sql, Student.class, sId);
    }


    public StudentVo queryStudentInfo(String number) {
        String sql = "SELECT s.number AS number,s.name AS sname,sex AS sex,m.majorName AS major,birthday AS birthday,address AS address,\n" +
                "phone AS phone,c.instructor AS instructor,c.cname AS className FROM student s INNER JOIN \n" +
                "major m ON s.m_id=m.m_id INNER JOIN class c ON s.s_class=c.c_id WHERE  s.number =?";
        return this.selectOne(sql, StudentVo.class, number);
    }

    public Student selectClassId(String number) {
        String sql = "SELECT s_id AS sId, s_class AS Sclass FROM student WHERE number=?";
        return this.selectOne(sql, Student.class, number);
    }

    public List<CurriculumVo> querycurriculumInfo(int cId) {
        String sql = "SELECT cu.work_id AS workId,cu.number AS number,co.name AS courseName,teacher AS teacher,\n" +
                "StartTime AS startTime,address AS address,class_hours AS classHours,credit AS credit FROM \n" +
                "curriculum cu  INNER JOIN course co ON cu.course_id=co.course_id WHERE classId=?";
        return this.selectList(sql, CurriculumVo.class, cId);
    }

    public List<ScoreVo> queryScoreInfo(int number) {
        String sql = "SELECT co.name AS courseName,cu.teacher AS teacherName,sc.fraction AS fraction\n" +
                "FROM score sc INNER JOIN course co ON sc.courseNum=co.course_id INNER JOIN curriculum cu ON\n" +
                "cu.cum_id=sc.cum_id WHERE sc.studentNum=?";
        return this.selectList(sql, ScoreVo.class, number);
    }

    public boolean delScore(Integer[] sId) {
        String sql = "DELETE FROM score WHERE studentNum=?";
        return this.batchDelete(sql, sId);
    }

    public boolean bathScore(String[] ids) {
        String sql = "DELETE FROM score WHERE studentNum=?";
        return this.batchDelete(sql, ids);
    }
}
